package com.itheima.dao.impl;

import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class UserDaoImpl implements UserDao {
    @Override
    public ArrayList<User> findAll() {

        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;
        ArrayList<User> userlist=null;

        try {
            //获取连接对象
            conn= JDBCUtils.getConnection();
            //需要的sql语句
            String sql="SELECT * FROM USER";
            //获取执行者对象
            pst=conn.prepareStatement(sql);
            //执行查询获取结果集对象
            rs=pst.executeQuery();
            userlist=new ArrayList<>();

            //处理结果集，将查询的每一条数据封装到user对象并存入集合
            while (rs.next()){
                //先创建一个user对象
                User u=new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setGender(rs.getString("gender"));
                u.setAge(rs.getInt("age"));
                u.setAddress(rs.getString("address"));
                u.setQq(rs.getInt("qq"));
                u.setEmail(rs.getString("email"));
                //添加到集合
                userlist.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,rs);
        }

        return userlist;
    }

    @Override
    public void add(User u) {
           Connection conn=null;
           PreparedStatement pst=null;

        try {
            //获取连接
            conn=JDBCUtils.getConnection();
            //需要的sql语句
            String sql="INSERT INTO USER VALUES(NULL,?,?,?,?,?,?,NULL,NULL)";
            //获取执行者对象
            pst=conn.prepareStatement(sql);

            //给？占位符赋值
            pst.setString(1,u.getName());
            pst.setString(2,u.getGender());
            pst.setInt(3,u.getAge());
            pst.setString(4,u.getAddress());
            pst.setInt(5,u.getQq());
            pst.setString(6,u.getEmail());

            //执行sql
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,null);
        }


    }

    @Override
    public void delete(String delId) {
        Connection conn=null;
        PreparedStatement pst=null;

        try {
            conn=JDBCUtils.getConnection();
            String sql="DELETE FROM USER WHERE id=?";
            pst=conn.prepareStatement(sql);
            //解析占位符
            pst.setInt(1,Integer.parseInt(delId));
            //执行sql语句
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,null);
        }


    }

    @Override
    public User findById(String upid) {
            Connection conn=null;
            PreparedStatement pst=null;
            ResultSet rs=null;
            User user=null;

        try {
            //获取连接
            conn=JDBCUtils.getConnection();
            //获取sql语句
            String sql="SELECT * FROM USER WHERE id=?";
            //获取执行者对象
            pst=conn.prepareStatement(sql);
            //解析占位符
            pst.setInt(1,Integer.parseInt(upid));
            //执行sql语句
            rs=pst.executeQuery();
            //处理结果集
            if (rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setGender(rs.getString("gender"));
                user.setAge(rs.getInt("age"));
                user.setAddress(rs.getString("address"));
                user.setQq(rs.getInt("qq"));
                user.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,rs);
        }

        return user;
    }

    @Override
    public void update(User user) {

        Connection conn=null;
        PreparedStatement pst=null;

        try {
            conn=JDBCUtils.getConnection();
            String sql="UPDATE USER SET NAME=?,gender=?,age=?,address=?,qq=?,email=? WHERE id=?";
            pst=conn.prepareStatement(sql);

            //给占位符赋值
            pst.setString(1,user.getName());
            pst.setString(2,user.getGender());
            pst.setInt(3,user.getAge());
            pst.setString(4,user.getAddress());
            pst.setInt(5,user.getQq());
            pst.setString(6,user.getEmail());
            pst.setInt(7,user.getId());

            //执行sql
            pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,null);
        }

    }

    @Override
    public User login(User u) {
            Connection conn=null;
            PreparedStatement pst=null;
            ResultSet rs=null;
            User user=null;

        try {
            //获取连接
            conn=JDBCUtils.getConnection();
            //拿到需要的sql语句
            String sql="SELECT * FROM USER WHERE username=? AND PASSWORD=?";
            //获取执行者对象
            pst=conn.prepareStatement(sql);
            //给占位符赋值
            pst.setString(1,u.getUsername());
            pst.setString(2,u.getPassword());
            //执行sql
            rs=pst.executeQuery();
            //处理结果集
            if (rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setGender(rs.getString("gender"));
                user.setAge(rs.getInt("age"));
                user.setAddress(rs.getString("address"));
                user.setQq(rs.getInt("qq"));
                user.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,pst,rs);
        }


        return user;
    }


}
